package cn.mydsl.restful

import com.mysql.cj.jdbc.exceptions.CommunicationsException
import io.vertx.ext.jdbc.JDBCClient
import io.vertx.ext.web.RoutingContext

/**
 * Created by xuybin on 17-2-14.
 */
class TableResource(val jdbcClient: JDBCClient, val defaultDbName:String) {
    companion object {
        //@Volatile可见性是指一个线程在写变量的时候,另外一个线程读这个变量读到的是修改后的值,并且读写该类型修饰的变量不会引起线程上下文的切换。
        @Volatile var fieldMap = mapOf<String, Field>()
        @Volatile var tableMap = mapOf<String, AuthMtadata>()
        @Volatile var isComplete = true
    }

    fun Ready():Boolean{
        return fieldMap.size>0 && tableMap.size>0
    }

    fun getInitializeMetadata(routingContext: RoutingContext): Unit {
        initializeMetadata(
                { routingContext.response().html("获取元数据,成功") },
                { jsonObject -> routingContext.response().html(jsonObject.toJsonString()) })
    }

    fun getTable(routingContext: RoutingContext): Unit {
        if (routingContext.request().getParam("table") != null) {
            var currentTableAuthMtadata = tableMap.find(defaultDbName, routingContext.request().getParam("table"), Keyword.SELECT)
            //服务端查找不到表，或表没有可用的字段，则报404
            if (currentTableAuthMtadata == null) {
                routingContext.next()//可能不是查询表
            }else if ( currentTableAuthMtadata.ableFields.size ==0) {
                routingContext.fail(403)//所有的字段，都禁止查询
            } else  {
                //准备需要joint的数据
                var jointTableList = listOf<JointAuthMtadata>()
                if (routingContext.request().getParam("joint") != null) {
                    jointTableList = tableMap.find(defaultDbName, routingContext.request().getParam("joint").split("|"), Keyword.SELECT, currentTableAuthMtadata.tableName, fieldMap)
                }
                //准备筛选和因为筛选引入的表数据
                val fiters = routingContext.request().fiters(defaultDbName, currentTableAuthMtadata.tableName, fieldMap)

                var sql: String
                val includeFiterFields = fiters.filter { s -> s.operat == Operat.INCL }
                val excludeFiterFields = fiters.filter { s -> s.operat == Operat.EXCL }
                var needTableNames = fiters.sqlNeedTableNames(currentTableAuthMtadata.tableName)
                if (includeFiterFields.size > 0 && includeFiterFields[0].fields.size > 0) {
                    //手动直接指定字段，相当于直接指定表，不考虑joint
                    sql = currentTableAuthMtadata.sqlSelectFromWhere(needTableNames, includeFiterFields[0].fields);
                } else if (excludeFiterFields.size > 0 && excludeFiterFields[0].fields.size > 0) {
                    //追加select字段from表，和 where 外键关联条件
                    //根据fiters查找出需要在form后关联出的表，并检查这些表是否joint（joint的话，需要输出字段，但fiters引入的表，无需带出字段）,
                    sql = currentTableAuthMtadata.sqlSelectFromWhere(jointTableList, needTableNames, excludeFiterFields[0].fields)
                } else {
                    //追加select字段from表，和 where 外键关联条件
                    //根据fiters查找出需要在form后关联出的表，并检查这些表是否joint（joint的话，需要输出字段，但fiters引入的表，无需带出字段）,
                    sql = currentTableAuthMtadata.sqlSelectFromWhere(jointTableList, needTableNames, listOf<FilterField>())
                }
                //追加用户权限，可操作范围
                //=======以下处理，拼装用户权限，可操作范围=================
                var whereRange = currentTableAuthMtadata.range
                val user=routingContext.user()
                if (whereRange != null && whereRange.isNotEmpty()) {
                    var matchResults = "\\[([\\w]+)\\]".toRegex().findAll(whereRange.trim())
                    var uid :String?=null
                    var eid :String?=null
                    var oid :String?=null
                    var jid :String?=null
                    var ocid :String?=null

                    if (user != null && user.principal() != null){
                        uid=user.principal().getString("uid")
                        eid=user.principal().getString("eid")
                        oid=user.principal().getString("oid")
                        if(oid==null){
                            oid=user.principal().getString("client_id")
                        }
                        ocid=user.principal().getString("ocid")
                        if(ocid==null){
                            ocid=user.principal().getString("clientOrgClass")
                        }
                        jid=user.principal().getString("jid")
                    }else {
                        routingContext.fail(401) //需要登录
                        return
                    }
                    for (groups in matchResults) {
                        var guserValue = groups.groups[1]
                        if (guserValue != null) {
                            when (guserValue.value.toLowerCase()) {
                                "uid" -> if(uid==null){
                                    routingContext.fail(Throwable("认证信息需要包含uid信息.")) //需要登录
                                    return
                                }else{
                                    whereRange = whereRange?.replace("[uid]", "'${uid}'")
                                }
                                "eid" -> if(eid==null){
                                    routingContext.fail(Throwable("认证信息需要包含uid信息.")) //需要登录
                                    return
                                }else{
                                    whereRange = whereRange?.replace("[eid]", "'${eid}'")
                                }

                                "oid" -> if(oid==null){
                                    routingContext.fail(Throwable("需要使用员工身份登录.")) //需要登录
                                    return
                                }else{
                                    whereRange = whereRange?.replace("[oid]", "'${oid}'")
                                }

                                "jid" -> if(jid==null){
                                    routingContext.fail(Throwable("认证信息需要包含job信息.")) //需要登录
                                    return
                                }else{
                                    //jid 是字符串数组
                                    whereRange = whereRange?.replace("[jid]", "'${jid.replace("'","").replace(",","','")}'")
                                }

                                "ocid" -> if(ocid==null){
                                    routingContext.fail(Throwable("认证信息需要包含OrgClass信息.")) //需要登录
                                    return
                                }else{
                                    //ocid 是字符串数组
                                    whereRange = whereRange?.replace("[ocid]", "'${ocid.replace("'","").replace(",","','")}'")
                                }
                                else -> {
                                    routingContext.fail(Throwable("还有未格式化的操作范围.：${currentTableAuthMtadata.range}")) //需要登录
                                    return
                                }
                            }
                        }
                    }
                    //追加where范围
                    if (whereRange != null && whereRange.isNotEmpty()) {
                        whereRange = "($whereRange)"
                    }
                }else{
                    whereRange=""
                }
                //=======以上处理，拼装用户权限，可操作范围=================
                //追加用户权限，可操作范围
                if (whereRange!=null && whereRange.isNotEmpty()) {
                    if (sql.trim().endsWith("WHERE", true)) {
                        sql += " $whereRange"
                    } else {
                        sql += " AND $whereRange"
                    }
                }
                //追加where筛选和排序

                val sqlAfterWhere = fiters.sqlAfterWhere()
                if (sqlAfterWhere.isNotEmpty()) {
                    if (sql.trim().endsWith("WHERE", true)) {
                        if (sqlAfterWhere.trim().startsWith("ORDER", true)) {
                            //WHERE 后无内容 且，紧跟ORDER
                            sql = "${sql.substringBeforeLast("WHERE")} $sqlAfterWhere"
                        } else {
                            sql += " $sqlAfterWhere" //WHERE 后无内容
                        }
                    } else if (sqlAfterWhere.trim().startsWith("ORDER", true)) {
                        sql += " $sqlAfterWhere" //WHERE 后有内容 且，紧跟ORDER
                    } else {
                        sql += " AND $sqlAfterWhere"//WHERE 后有内容当前 有筛选,且筛选部分，已经被（）包裹
                    }
                }

                //检查WHERE是否填充
                if (sql.trim().endsWith("WHERE", true)) {
                    sql = sql.substringBeforeLast("WHERE")
                }

                //追加分页
                val size = routingContext.request().size()
                val page = routingContext.request().page()
                if (size != null && page != null) {
                    sql = "$sql LIMIT $size OFFSET ${(page - 1) * size}"
                }
                //开始查询
                jdbcClient.getConnection({ ar ->
                    if (ar.failed()) {
                        var erroMsg=ar.cause().toErroMsg("从JDBC连接池获取连接失败",ErroType.SqlConExcp)
                        erroMsg.printJsonString()
                        routingContext.response().erro(erroMsg)
                    } else {
                        val connection = ar.result()
                        connection.query(sql, { result ->

                            if (result.failed()) {
                                println("sql：${sql}")
                                if (result.cause() is CommunicationsException) {
                                    var erroMsg=result.cause().toErroMsg("数据库连接丢失",ErroType.SqlConExcp)
                                    erroMsg.printJsonString()
                                    routingContext.response().erro(erroMsg)
                                } else {
                                    var erroMsg=result.cause().toErroMsg("执行sql失败",ErroType.SqlExecExcp)
                                    erroMsg.printJsonString()
                                    routingContext.response().erro(erroMsg)
                                }
                            } else {
                                val rows = result.result().getRows()
                                println("sql：${sql} return ${rows.size}")
                                routingContext.response().html(rows.toJsonString())
                                connection.close()
                            }
                        })
                    }
                })
            }
        }
    }

    //数据库元数据
    fun initializeMetadata( succeed: () -> Unit, fail: (erroMsg: ErroMsg) -> Unit = { j -> }): Unit {
        if (isComplete) {
            isComplete = false
            jdbcClient.getConnection({ ar ->
                if (ar.failed()) {
                    isComplete = true
                    var erroMsg=ar.cause().toErroMsg("获取元数据,失败：获取连接失败",ErroType.SqlConExcp)
                    erroMsg.printJsonString()
                    fail.invoke(erroMsg)
                } else {
                    val connection = ar.result()
                    var tableHashMap = hashMapOf<String, AuthMtadata>()//临时转储表map
                    var sqlStr = ""


                    /********************************获取数据库元数据**************************************/
                    connection.query("SELECT `table_schema`,`table_name`,`column_name`,`data_type` FROM `information_schema`.`columns` WHERE `table_schema` NOT IN ('information_schema','mysql','performance_schema','sys') AND `table_name`<>'AuthMtadata' ORDER BY `table_schema`,`table_name`;", { result ->
                        if (result.failed()) {
                            isComplete = true
                            if (result.cause() is CommunicationsException) {
                                var erroMsg=result.cause().toErroMsg("获取元数据,失败：数据库连接丢失",ErroType.SqlConExcp)
                                erroMsg.printJsonString()
                                fail.invoke(erroMsg)
                            } else {
                                var erroMsg=result.cause().toErroMsg("获取元数据,失败：执行sql失败",ErroType.SqlExecExcp)
                                erroMsg.printJsonString()
                                fail.invoke(erroMsg)
                            }
                        } else {
                            val rows = result.result().getRows()
                            var fieldHashMap = hashMapOf<String, Field>()//临时转储字段map
                            var lastDBName = ""

                            var fieldList = mutableListOf<Field>()
                            var lastTableName = ""

                            for (row in rows) {
                                var tableName = "`${row.getString("table_schema")}`.`${row.getString("table_name")}`"
                                var fieldName = "$tableName.`${row.getString("column_name")}`"
                                var datType = row.getString("data_type");

                                if (lastDBName != row.getString("table_schema")) {
                                    lastDBName = row.getString("table_schema")
                                    sqlStr += "SELECT '${lastDBName}' as dbName, `tableName`,`keyword`,`disableField`,`range` FROM `$lastDBName`.`AuthMtadata` UNION "
                                }

                                //添加tableName->可使用的字段信息
                                if (lastTableName != tableName) {
                                    if (lastTableName != "") {
                                        tableHashMap.put(lastTableName + Keyword.SELECT.VALUE, AuthMtadata(lastTableName, Keyword.SELECT, fieldList.toList()))
                                        tableHashMap.put(lastTableName + Keyword.INSERT.VALUE, AuthMtadata(lastTableName, Keyword.INSERT, fieldList.toList()))
                                        tableHashMap.put(lastTableName + Keyword.UPDATE.VALUE, AuthMtadata(lastTableName, Keyword.UPDATE, fieldList.toList()))
                                        tableHashMap.put(lastTableName + Keyword.DELETE.VALUE, AuthMtadata(lastTableName, Keyword.DELETE, fieldList.toList()))
                                        fieldList.clear()
                                    }
                                    lastTableName = tableName
                                }
                                //添加字段 fieldName->字段信息
                                if (datType.contains("date", true) || datType.contains("time", true)) {
                                    var field = Field(fieldName, FieldType.DATA)
                                    fieldList.add(field)
                                    fieldHashMap.put(fieldName, field)
                                } else if (datType.contains("int", true) || datType.equals("float", true) || datType.equals("double", true) || datType.equals("decimal", true)) {
                                    var field = Field(fieldName, FieldType.NUMBER)
                                    fieldList.add(field)
                                    fieldHashMap.put(fieldName, field)
                                } else {
                                    var field = Field(fieldName, FieldType.STRING)
                                    fieldList.add(field)
                                    fieldHashMap.put(fieldName, field)
                                }
                            }
                            //保存最后一次,ableName->可使用的字段信息
                            if (lastTableName != "") {
                                tableHashMap.put(lastTableName + Keyword.SELECT.VALUE, AuthMtadata(lastTableName, Keyword.SELECT, fieldList.toList()))
                                tableHashMap.put(lastTableName + Keyword.INSERT.VALUE, AuthMtadata(lastTableName, Keyword.INSERT, fieldList.toList()))
                                tableHashMap.put(lastTableName + Keyword.UPDATE.VALUE, AuthMtadata(lastTableName, Keyword.UPDATE, fieldList.toList()))
                                tableHashMap.put(lastTableName + Keyword.DELETE.VALUE, AuthMtadata(lastTableName, Keyword.DELETE, fieldList.toList()))
                                fieldList.clear()
                            }
                            //connection.close()
                            //因为Volatile修饰,所以保证变动被其他线程访问到
                            fieldMap = fieldHashMap
                            //isComplete = true
                            println("获取数据库元数据,成功")
                            //succeed.invoke()
                            /********************************获取数据库元数据**************************************/


                            /********************************获取认证元数据**************************************/
                            sqlStr = sqlStr.substringBeforeLast("UNION")
                            connection.query(sqlStr, { result ->
                                if (result.failed()) {
                                    isComplete = true
                                    if (result.cause() is CommunicationsException) {
                                        var erroMsg=result.cause().toErroMsg("获取元数据,失败：数据库连接丢失",ErroType.SqlConExcp)
                                        erroMsg.printJsonString()
                                        fail.invoke(erroMsg)
                                    } else {
                                        var erroMsg=result.cause().toErroMsg("获取元数据,失败：执行sql失败",ErroType.SqlExecExcp)
                                        erroMsg.printJsonString()
                                        fail.invoke(erroMsg)
                                    }
                                } else {
                                    val rows2 = result.result().getRows()
                                    for (row in rows2) {
                                        var table = "`${row.getString("dbName").replace("`", "")}`.`${row.getString("tableName").replace("`", "")}`"
                                        var key = table + row.getString("keyword").toUpperCase().replace(" ", "")
                                        var disableFields = row.getString("disableField")
                                        var range = row.getString("range")
                                        var authMtadata = tableHashMap[key]
                                        if (authMtadata != null) {
                                            authMtadata.range = range
                                            if (disableFields != null && disableFields.isNotEmpty()) {
                                                disableFields = disableFields.replace("`", "")
                                                var ableFields = authMtadata.ableFields
                                                //有需要过滤的字段
                                                for (disableField in disableFields.split("|")) {
                                                    ableFields = ableFields.filter { field -> !field.name.equals("$table.`$disableField`") }
                                                }
                                                //过滤后的值，重新赋值给MAP
                                                authMtadata.ableFields = ableFields
                                            }
                                        }
                                    }
                                    connection.close()
                                    //因为Volatile修饰,所以保证变动被其他线程访问到
                                    tableMap = tableHashMap
                                    isComplete = true
                                    println("获取认证元数据,成功")
                                    succeed.invoke()
                                }
                            })
                            /********************************获取认证元数据**************************************/
                        }
                    })
                }
            })
        } else {
            fail.invoke(ErroMsg("获取元数据,上一次未完成",ErroType.InvalidParam))
        }
    }


}